﻿using CaltechSoft.Service.Infrastructure;
using CaltechSoft.Service.Models;
using CaltechSoft.Service.SearchCriteria;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;

namespace CaltechSoft.Service.ReportService.Certificate
{
    class GetCertificateDTOBySearchCriteriaDbCommand : ICommand<SearchResult<CertificateDTO>>
    {
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private static CertificateSearchCriteria _criteria;

        private const string _sql = @"
              WITH PAGING AS (
                select C.*, U.Name,U.[Address], row_number() over (order by {0}) as rownumber from Certificate C 
                left join Customers U on C.CustomerId = U.Id 
            {1})
        ";

        public GetCertificateDTOBySearchCriteriaDbCommand(CertificateSearchCriteria criteria, int pageSize, int pageIndex)
        {
            _pageSize = pageSize;
            _pageIndex = pageIndex;
            _criteria = criteria;
        }

        public SearchResult<CertificateDTO> Execute()
        {

            var connectionString = ConfigurationSettings.AppSettings["DefaultConnection"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                IList<CertificateDTO> items = new List<CertificateDTO>();
                int totalCount = 0;
                SqlCommand cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalCount = reader.GetInt32(0);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader));
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new SearchResult<CertificateDTO>(items, totalCount);
            }
        }
        private CertificateDTO GetDto(SqlDataReader reader)
        {
            return new CertificateDTO
            {
                CertificateId = (int)reader["CertificateId"],
                CustomerName = reader["Name"].ToString(),
                SerialNumber = reader["SerialNumber"].ToString(),
                Description = reader["Description"] != DBNull.Value ? reader["Description"].ToString() : string.Empty,
                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                Manufacturer = reader["Manufacturer"].ToString(),
                CertificateNo = reader["CertificateNo"] != DBNull.Value ? reader["CertificateNo"].ToString() : string.Empty,
                WorkOrderNo = reader["WorkOrderNo"] != DBNull.Value ? reader["WorkOrderNo"].ToString() : string.Empty,
                CalibrationDate = reader["CalibrationDate"] != DBNull.Value ? Convert.ToDateTime(reader["CalibrationDate"]) : DateTime.MinValue,
            };
        }

        private string BuildQuery()
        {
            var query = new StringBuilder();
            var sqlQuery = string.Format(_sql, BuildOrderClause(), BuildWhereClause());
            query.AppendFormat("{0} {1};{0} {2}", sqlQuery, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

        private string BuildWhereClause()
        {
            string whereClause = string.Empty;

            if (!string.IsNullOrEmpty(_criteria.CustomerName))
            {
                whereClause = String.Format("U.Name LIKE '%{0}%'", _criteria.CustomerName.Trim());
            }

            if (!string.IsNullOrEmpty(_criteria.SerialNumber))
            {
                whereClause = String.Format("C.SerialNumber LIKE '%{0}%'", _criteria.SerialNumber.Trim());
            }

            if (!string.IsNullOrEmpty(_criteria.CertificateNumber))
            {
                whereClause = String.Format("C.CertificateNo LIKE '%{0}%'", _criteria.CertificateNumber.Trim());
            }

            if (!string.IsNullOrEmpty(_criteria.ModelNumber))
            {
                whereClause = String.Format("C.ModelNumber LIKE '%{0}%'", _criteria.ModelNumber.Trim());
            }

            

            if (_criteria.CustomerId != null)
            {
                whereClause += String.Format(("AND C.CustomerId = '{0}'"), _criteria.CustomerId);
            }

            if (!string.IsNullOrEmpty(_criteria.Username))
            {
                whereClause += String.Format("AND U.LoginUsername = '{0}'", _criteria.Username.Trim());
            }

            if (!string.IsNullOrEmpty(_criteria.WorkOrderNo))
            {
                whereClause = String.Format("AND C.WorkOrderNo LIKE '%{0}%'", _criteria.WorkOrderNo.Trim());
            }

            if (whereClause.Trim().StartsWith("AND", StringComparison.InvariantCultureIgnoreCase))
            {
                whereClause = whereClause.Trim().Remove(0, 3);
            }

            if (!String.IsNullOrEmpty(whereClause))
            {
                return " WHERE " + whereClause;
            }

            return string.Empty;
        }

        private static string BuildOrderClause()
        {
            StringBuilder orderClause = new StringBuilder();
            foreach (SearchSortItem sortItem in _criteria.SortItems)
            {
                orderClause.AppendFormat("i.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                orderClause.Append(',');
            }

            if (orderClause.Length <= 0)
            {
                orderClause.Append("C.CreatedDate DESC");
            }
            else
            {
                orderClause.Remove(orderClause.Length - 1, 1);
            }
            return orderClause.ToString();
        }
    }

}
